﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using trangbaocao.Models;
using System.Data;
using System.Data.OracleClient;
using Microsoft.Reporting.WebForms;

namespace trangbaocao.Reports.Fuel.FuelGenerator
{
    public partial class ReportGeneratorLevel : System.Web.UI.Page
    {
        StationsModel StationsModel = new StationsModel();
        Main_StationsModel Main_StationsModel = new Main_StationsModel();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                if (Session != null && Convert.ToInt32(Session["quyen_id"]) > 2)
                {
                    Radio1.Visible = false;
                }
                List<Main_StationsModel> main_StationsModel = Main_StationsModel.getMain_Stations();
                List<StationsModel> stationModel = StationsModel.getStationsByMain_Stations(Convert.ToInt32(main_StationsModel[0].id));
                cbtrungtam.DataSource = main_StationsModel;
                cbtrungtam.DataTextField = "name";
                cbtrungtam.DataValueField = "id";
                cbtrungtam.DataBind();

                cbtram.DataSource = stationModel;
                cbtram.DataTextField = "name";
                cbtram.DataValueField = "id";
                cbtram.DataBind();

                cbnam.DataSource = getYear();
                cbnam.DataTextField = "nam";
                cbnam.DataValueField = "nam";
                cbnam.DataBind();

                for (int i = 1; i <= 12; i++)
                {
                    cbthang.Items.Add(i.ToString());
                    cbthang2.Items.Add(i.ToString());
                }
                //cbthang.Items.FindByValue(DateTime.Now.Month.ToString()).Selected = true;
                cbthang2.Items.FindByValue(DateTime.Now.Month.ToString()).Selected = true;
            }
        }

        protected void radio_tram_group_CheckedChanged(object sender, EventArgs e)
        {
            if (radio_tram_group.Checked)
            {
                cbtram.Enabled = false;
            }
            if (radio_tram.Checked)
            {
                cbtram.Enabled = true;
            }
        }

        protected void Radio1_CheckedChanged(object sender, EventArgs e)
        {
            if (Radio1.Checked)
            {
                cbtrungtam.Enabled = false;
                cbtram.Enabled = false;
            }
            if (Radio2.Checked)
            {
                cbtrungtam.Enabled = true;
                cbtram.Enabled = true;
            }
        }

        protected void cbtrungtam_SelectedIndexChanged(object sender, EventArgs e)
        {
            List<StationsModel> stationModel = StationsModel.getStationsByMain_Stations(Convert.ToInt32(cbtrungtam.SelectedValue));
            cbtram.DataSource = stationModel;
            cbtram.DataTextField = "name";
            cbtram.DataValueField = "id";
            cbtram.DataBind();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            ReportViewer1.Reset();
            Main_Sort_DeviceModel Main_Sort_DeviceModel = new Main_Sort_DeviceModel();
            List<Main_Sort_DeviceModel> main_Sort_DeviceModel = Main_Sort_DeviceModel.getChungloaithietbi();

            ReportViewer1.LocalReport.ReportPath = "Reports/Fuel/FuelGenerator/ReportGeneratorLevel.rdlc";
            ReportViewer1.LocalReport.DataSources.Clear();
            ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("generator_level_dataset", getGeneratorActivityProgressive().Tables[0]));

            ReportViewer1.DataBind();
            ReportViewer1.LocalReport.Refresh();
        }
        public DataSet getGeneratorActivityProgressive()
        {
            string sql = "";
            string nam = cbnam.SelectedValue;
            string thang = cbthang.SelectedValue;
            string thang2 = cbthang2.SelectedValue;
            string trung_tam_id = cbtrungtam.SelectedValue;
            string tram_id = cbtram.SelectedValue;

            string sql_tram = "";
            if (radio_tram.Checked)
                sql_tram = " and d.id='" + tram_id + "'";
            if (Radio2.Checked)
            {
                sql += "select a.*,b.value_using dinh_muc_tinh_toan from (";

                //sql += "select b.device_stations_id,b.value_using  from";
                //sql += " (select id,device_id,name_parameter from parameterrs_base where pra_meba_sort_dv_id=8)a,  ";
                //sql += "(select * from parameter_using ) b where a.id=b.parameter_base_id ";

                sql +=" select device_stations_id,decode(tri_so1,0,1,tri_so1)*decode(tri_so2,0,1,tri_so2)*decode(tri_so3,0,1,tri_so3)*decode(tri_so4,0,1,tri_so4)*decode(tri_so5,0,1,tri_so5) value_using from";
                sql += " (select device_stations_id,sum(tri_so1) tri_so1,sum(tri_so2) tri_so2,";
                sql +=" sum(tri_so3) tri_so3,sum(tri_so4) tri_so4,sum(tri_so5) tri_so5 from";
                sql +=" (select device_stations_id,tri_so1,tri_so2,tri_so3,tri_so4,tri_so5 from";
                sql += " (select parameter_base_id,device_stations_id,TO_NUMBER(value_using) tri_so1,0 tri_so2,";
                sql +=" 0 tri_so3,0 tri_so4,0 tri_so5 from parameter_using ) a,";
                sql +=" (select id from parameterrs_base where pra_meba_sort_dv_id=2) b";
                sql +=" where a.parameter_base_id=b.id";
                sql +=" union";
                sql +=" select device_stations_id,tri_so1,tri_so2,tri_so3,tri_so4,tri_so5 from";
                sql += " (select parameter_base_id,device_stations_id,0 tri_so1,TO_NUMBER(value_using) tri_so2,";
                sql +=" 0 tri_so3,0 tri_so4,0 tri_so5 from parameter_using ) a,";
                sql +=" (select id from parameterrs_base where pra_meba_sort_dv_id=3) b";
                sql +=" where a.parameter_base_id=b.id";
                sql +=" union";
                sql +=" select device_stations_id,tri_so1,tri_so2,tri_so3,tri_so4,tri_so5 from";
                sql +=" (select parameter_base_id,device_stations_id,0 tri_so1,0 tri_so2,";
                sql += " TO_NUMBER(value_using) tri_so3,0 tri_so4,0 tri_so5 from parameter_using ) a,";
                sql +=" (select id from parameterrs_base where pra_meba_sort_dv_id=4) b";
                sql +=" where a.parameter_base_id=b.id";
                sql +=" union";
                sql +=" select device_stations_id,tri_so1,tri_so2,tri_so3,tri_so4,tri_so5 from";
                sql +=" (select parameter_base_id,device_stations_id,0 tri_so1,0 tri_so2,";
                sql += " 0 tri_so3,TO_NUMBER(value_using) tri_so4,0 tri_so5 from parameter_using ) a,";
                sql +=" (select id from parameterrs_base where pra_meba_sort_dv_id=5) b";
                sql +=" where a.parameter_base_id=b.id";
                sql +=" union";
                sql +=" select device_stations_id,tri_so1,tri_so2,tri_so3,tri_so4,tri_so5 from";
                sql +=" (select parameter_base_id,device_stations_id,0 tri_so1,0 tri_so2,";
                sql += " 0 tri_so3,0 tri_so4,TO_NUMBER(value_using) tri_so5 from parameter_using ) a,";
                sql +=" (select id from parameterrs_base where pra_meba_sort_dv_id=7) b";
                sql +=" where a.parameter_base_id=b.id) a";
                sql +=" group by device_stations_id) a";

                sql += ")b,(select a.device_stations_id,b.name,b.ma_he_thong ma_thiet_bi,d.name ten_tram,c.name ten_trung_tam,a.tong_nhap_su_dung so_lit_chay_may,a.tong_nhap_bao_duong so_lit_bao_duong,";
                sql += "a.tong_thoi_gian gio_chay_may,a.dinh_muc,a.stations_id,a.main_stations_id from(";
                sql += "     select a.device_stations_id,sum(tong_nhap_su_dung) tong_nhap_su_dung,";
                sql += "     sum(a.tong_nhap_bao_duong) tong_nhap_bao_duong,a.tong_thoi_gian,";
                sql += "     sum(a.dinh_muc) dinh_muc,a.stations_id,a.main_stations_id from(";
                sql += "         select  a.device_stations_id, ";
                sql += "         decode(a.fuel_status,0,a.tong_nhap,'') tong_nhap_su_dung,decode(a.fuel_status,1,a.tong_nhap,'') tong_nhap_bao_duong";
                sql += "         ,a.tong_thoi_gian,a.dinh_muc,a.stations_id,a.main_stations_id from(";
                sql += "             select a.device_stations_id,a.tong_nhap,b.tong_thoi_gian,";
                sql += "             decode(a.fuel_status,0,round(a.tong_nhap/b.tong_thoi_gian,2),'') dinh_muc";
                sql += "             ,a.stations_id,a.main_stations_id,a.fuel_status from";
                sql += "             (SELECT a.device_stations_id, sum(a.nhap_trong_thang) tong_nhap, a.stations_id,a.main_stations_id,fuel_status";
                sql += "             FROM fuel a where thang BETWEEN " + thang + " and " + thang2 + " and nam=" + nam + " group by device_stations_id,main_stations_id,stations_id,fuel_status) a,          ";
                sql += "             (SELECT a.device_stations_id, ROUND(sum(a.thoi_gian_hd)/60,2) tong_thoi_gian,a.main_stations_id, a.stations_id       ";
                sql += "             FROM fuel_detail a where  thang BETWEEN " + thang + " and " + thang2 + " and nam=" + nam + " group by device_stations_id,main_stations_id,stations_id) b";
                sql += "             where  a.device_stations_id=b.device_stations_id";
                sql += "          )a";
                sql += "      )a";
                sql += "      group by a.device_stations_id,a.main_stations_id,a.stations_id,a.tong_thoi_gian";
                sql += "  )a,device_stations b,main_stations c,stations d";
                sql += "  where a.device_stations_id=b.id and a.main_stations_id=c.id and a.stations_id=d.id and c.id="+trung_tam_id+sql_tram;
                sql += " )a where a.device_stations_id=b.device_stations_id   ";
            }
            if (Radio1.Checked)
            {
                   //sql += "select a.*,b.value_using dinh_muc_tinh_toan from (select b.device_stations_id,b.value_using  from";
                   //sql += " (select id,device_id,name_parameter from parameterrs_base where pra_meba_sort_dv_id=8)a,  ";
                   //sql += "(select * from parameter_using ) b where a.id=b.parameter_base_id)b, ";
                   sql += "select a.*,b.value_using dinh_muc_tinh_toan from (";

                   sql += " select device_stations_id,decode(tri_so1,0,1,tri_so1)*decode(tri_so2,0,1,tri_so2)*decode(tri_so3,0,1,tri_so3)*decode(tri_so4,0,1,tri_so4)*decode(tri_so5,0,1,tri_so5) value_using from";
                   sql += " (select device_stations_id,sum(tri_so1) tri_so1,sum(tri_so2) tri_so2,";
                   sql += " sum(tri_so3) tri_so3,sum(tri_so4) tri_so4,sum(tri_so5) tri_so5 from";
                   sql += " (select device_stations_id,tri_so1,tri_so2,tri_so3,tri_so4,tri_so5 from";
                   sql += " (select parameter_base_id,device_stations_id,TO_NUMBER(value_using) tri_so1,0 tri_so2,";
                   sql += " 0 tri_so3,0 tri_so4,0 tri_so5 from parameter_using ) a,";
                   sql += " (select id from parameterrs_base where pra_meba_sort_dv_id=2) b";
                   sql += " where a.parameter_base_id=b.id";
                   sql += " union";
                   sql += " select device_stations_id,tri_so1,tri_so2,tri_so3,tri_so4,tri_so5 from";
                   sql += " (select parameter_base_id,device_stations_id,0 tri_so1,TO_NUMBER(value_using) tri_so2,";
                   sql += " 0 tri_so3,0 tri_so4,0 tri_so5 from parameter_using ) a,";
                   sql += " (select id from parameterrs_base where pra_meba_sort_dv_id=3) b";
                   sql += " where a.parameter_base_id=b.id";
                   sql += " union";
                   sql += " select device_stations_id,tri_so1,tri_so2,tri_so3,tri_so4,tri_so5 from";
                   sql += " (select parameter_base_id,device_stations_id,0 tri_so1,0 tri_so2,";
                   sql += " TO_NUMBER(value_using) tri_so3,0 tri_so4,0 tri_so5 from parameter_using ) a,";
                   sql += " (select id from parameterrs_base where pra_meba_sort_dv_id=4) b";
                   sql += " where a.parameter_base_id=b.id";
                   sql += " union";
                   sql += " select device_stations_id,tri_so1,tri_so2,tri_so3,tri_so4,tri_so5 from";
                   sql += " (select parameter_base_id,device_stations_id,0 tri_so1,0 tri_so2,";
                   sql += " 0 tri_so3,TO_NUMBER(value_using) tri_so4,0 tri_so5 from parameter_using ) a,";
                   sql += " (select id from parameterrs_base where pra_meba_sort_dv_id=5) b";
                   sql += " where a.parameter_base_id=b.id";
                   sql += " union";
                   sql += " select device_stations_id,tri_so1,tri_so2,tri_so3,tri_so4,tri_so5 from";
                   sql += " (select parameter_base_id,device_stations_id,0 tri_so1,0 tri_so2,";
                   sql += " 0 tri_so3,0 tri_so4,TO_NUMBER(value_using) tri_so5 from parameter_using ) a,";
                   sql += " (select id from parameterrs_base where pra_meba_sort_dv_id=7) b";
                   sql += " where a.parameter_base_id=b.id) a";
                   sql += " group by device_stations_id) a";

                   sql += ")b,(select a.device_stations_id,b.name,b.ma_he_thong ma_thiet_bi,d.name ten_tram,c.name ten_trung_tam,a.tong_nhap_su_dung so_lit_chay_may,a.tong_nhap_bao_duong so_lit_bao_duong,";
                   sql += "a.tong_thoi_gian gio_chay_may,a.dinh_muc,a.stations_id,a.main_stations_id from(";
                   sql += "     select a.device_stations_id,sum(tong_nhap_su_dung) tong_nhap_su_dung,";
                   sql += "     sum(a.tong_nhap_bao_duong) tong_nhap_bao_duong,a.tong_thoi_gian,";
                   sql += "     sum(a.dinh_muc) dinh_muc,a.stations_id,a.main_stations_id from(";
                   sql += "         select  a.device_stations_id, ";
                   sql += "         decode(a.fuel_status,0,a.tong_nhap,'') tong_nhap_su_dung,decode(a.fuel_status,1,a.tong_nhap,'') tong_nhap_bao_duong";
                   sql += "         ,a.tong_thoi_gian,a.dinh_muc,a.stations_id,a.main_stations_id from(";
                   sql += "             select a.device_stations_id,a.tong_nhap,b.tong_thoi_gian,";
                   sql += "             decode(a.fuel_status,0,round(a.tong_nhap/b.tong_thoi_gian,2),'') dinh_muc";
                   sql += "             ,a.stations_id,a.main_stations_id,a.fuel_status from";
                   sql += "             (SELECT a.device_stations_id, sum(a.nhap_trong_thang) tong_nhap, a.stations_id,a.main_stations_id,fuel_status";
                   sql += "             FROM fuel a where thang BETWEEN " + thang + " and " + thang2 + " and nam=" + nam + " group by device_stations_id,main_stations_id,stations_id,fuel_status) a,          ";
                   sql += "             (SELECT a.device_stations_id, ROUND(sum(a.thoi_gian_hd)/60,2) tong_thoi_gian,a.main_stations_id, a.stations_id       ";
                   sql += "             FROM fuel_detail a where  thang BETWEEN "+thang+" and "+thang2+" and nam="+nam+" group by device_stations_id,main_stations_id,stations_id) b";
                   sql += "             where  a.device_stations_id=b.device_stations_id";
                   sql += "          )a";
                   sql += "      )a";
                   sql += "      group by a.device_stations_id,a.main_stations_id,a.stations_id,a.tong_thoi_gian";
                   sql += "  )a,device_stations b,main_stations c,stations d";
                   sql += "  where a.device_stations_id=b.id and a.main_stations_id=c.id and a.stations_id=d.id";
                   sql += " )a where a.device_stations_id=b.device_stations_id   ";
            }

            OracleConnection conn = General.ConnectDataBase();
            OracleCommand omd = new OracleCommand(sql, conn);
            OracleDataAdapter oda = new OracleDataAdapter(omd);
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
            }
            catch
            {
                System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE='JavaScript'>alert('Lỗi kết nối với cơ sở dữ liệu!')</SCRIPT>");
            }
            oda.Fill(ds, "0");
            conn.Close();
            return ds;
        }
        public DataSet getYear()
        {
            string sql = "SELECT distinct nam from fuel order by nam desc";
            OracleConnection conn = General.ConnectDataBase();
            OracleCommand omd = new OracleCommand(sql, conn);
            OracleDataAdapter oda = new OracleDataAdapter(omd);
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
            }
            catch
            {
                System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE='JavaScript'>alert('Lỗi kết nối với cơ sở dữ liệu!')</SCRIPT>");
            }
            oda.Fill(ds, "0");
            conn.Close();
            return ds;
        }
        
   //select a.device_stations_id,b.name,b.ma_he_thong,d.name ten_tram,c.name ten_trung_tam,a.tong_nhap_su_dung,a.tong_nhap_bao_duong,
   //a.tong_thoi_gian,a.dinh_muc,a.stations_id,a.main_stations_id from(
   //     select a.device_stations_id,sum(tong_nhap_su_dung) tong_nhap_su_dung,
   //     sum(a.tong_nhap_bao_duong) tong_nhap_bao_duong,a.tong_thoi_gian,
   //     sum(a.dinh_muc) dinh_muc,a.stations_id,a.main_stations_id from(
   //         select  a.device_stations_id, 
   //         decode(a.fuel_status,0,a.tong_nhap,'') tong_nhap_su_dung,decode(a.fuel_status,1,a.tong_nhap,'') tong_nhap_bao_duong
   //         ,a.tong_thoi_gian,a.dinh_muc,a.stations_id,a.main_stations_id from(
   //             select a.device_stations_id,a.tong_nhap,b.tong_thoi_gian,
   //             decode(a.fuel_status,0,round(a.tong_nhap/b.tong_thoi_gian,2),'') dinh_muc
   //             ,a.stations_id,a.main_stations_id,a.fuel_status from
   //             (SELECT a.device_stations_id, sum(a.nhap_trong_thang) tong_nhap, a.stations_id,a.main_stations_id,fuel_status
   //             FROM fuel a where thang BETWEEN 1 and 2 and nam=2014 group by device_stations_id,main_stations_id,stations_id,fuel_status) a,          
   //             (SELECT a.device_stations_id, ROUND(sum(a.thoi_gian_hd)/60,2) tong_thoi_gian,a.main_stations_id, a.stations_id       
   //             FROM fuel_detail a where  thang BETWEEN 1 and 2 and nam=2014 group by device_stations_id,main_stations_id,stations_id) b
   //             where  a.device_stations_id=b.device_stations_id
   //          )a
   //      )a
   //      group by a.device_stations_id,a.main_stations_id,a.stations_id,a.tong_thoi_gian
   //  )a,device_stations b,main_stations c,stations d
   //  where a.device_stations_id=b.id and a.main_stations_id=c.id and a.stations_id=d.id
     
    }
}